/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.core.dao.HistoryDAO;
import com.core.database.Manager;
import com.core.entities.History;
import com.core.exception.DAOException;

/**
 * 
 * @author himanasser
 */
public class HistoryDAOImpl implements HistoryDAO {
	ArrayList<History> historysList = new ArrayList<History>();

History history = new History();
	Connection connection = null;
	private static HistoryDAOImpl instance=null;
	public static HistoryDAOImpl getInstance(){
		if(instance==null){
			instance=new HistoryDAOImpl();
		}
		return instance;
	}

	@Override
	public List<History> findByReservation_id(int reservation_id) throws DAOException {		
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM history WHERE reservation_id = '"
					+ reservation_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				history.setId(rs.getInt(1));
				history.setReservation_id(rs.getInt(2));
				history.setPatient_id(rs.getInt(3));
				history.setDoctor_id(rs.getInt(4));
				history.setComment(rs.getString(5));
				history.setBill_id(rs.getInt(6));

				historysList.add(history);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return historysList;
	}

	@Override
	public List<History> findByPatient_id(int patient_id) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM history WHERE patient_id = '"
					+ patient_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				history.setId(rs.getInt(1));
				history.setReservation_id(rs.getInt(2));
				history.setPatient_id(rs.getInt(3));
				history.setDoctor_id(rs.getInt(4));
				history.setComment(rs.getString(5));
				history.setBill_id(rs.getInt(6));

				historysList.add(history);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return historysList;
	}

	@Override
	public List<History> findByDoctor_id(int doctor_id) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM history WHERE doctor_id = '"
					+ doctor_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				history.setId(rs.getInt(1));
				history.setReservation_id(rs.getInt(2));
				history.setPatient_id(rs.getInt(4));
				history.setDoctor_id(rs.getInt(5));
				history.setComment(rs.getString(6));
				history.setBill_id(rs.getInt(7));
				historysList.add(history);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return historysList;
	}

	@Override
	public List<History> findByComment(String comment) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT *FROM history WHERE comment = '" + comment
					+ "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {

					history.setId(rs.getInt(1));
					history.setReservation_id(rs.getInt(2));
					history.setPatient_id(rs.getInt(4));
					history.setDoctor_id(rs.getInt(5));
					history.setComment(rs.getString(6));
					history.setBill_id(rs.getInt(7));
					historysList.add(history);

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return historysList;
	}

	@Override
	public List<History> findByBill_id(int bill_id) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM history WHERE bill_id = '" + bill_id
					+ "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				history.setId(rs.getInt(1));
				history.setReservation_id(rs.getInt(2));
				history.setPatient_id(rs.getInt(3));
				history.setDoctor_id(rs.getInt(4));
				history.setComment(rs.getString(5));
				history.setBill_id(rs.getInt(6));

				historysList.add(history);
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return historysList;
	}

	@Override
	public void insert(History o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "INSERT INTO history (reservation_id, patient_id, doctor_id, comment,"
					+ "bill_id  ) VALUE (?, ? )";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setInt(1, o.getReservation_id());
			ps.setInt(2, o.getPatient_id());
			ps.setInt(3, o.getDoctor_id());
			ps.setString(4, o.getComment());
			ps.setInt(5, o.getBill_id());

			int result = ps.executeUpdate();
			if (result != 1) {
				throw new SQLException("no row inserted!");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new RuntimeException("Cannot add new History", ex);
		} finally {
			Manager.closeConnection(connection);
		}
	}

	@Override
	public History findById(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT *FROM history WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					history.setId(rs.getInt(1));
					history.setReservation_id(rs.getInt(2));
					history.setPatient_id(rs.getInt(3));
					history.setDoctor_id(rs.getInt(4));
					history.setComment(rs.getString(5));
					history.setBill_id(rs.getInt(6));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return history;
	}

	@Override
	public List<History> findAll() throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " SELECT * FROM history ";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				history.setId(rs.getInt(1));
				history.setReservation_id(rs.getInt(2));
				history.setPatient_id(rs.getInt(3));
				history.setDoctor_id(rs.getInt(4));
				history.setComment(rs.getString(5));
				history.setBill_id(rs.getInt(6));
				historysList.add(history);
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return historysList;
	}

	@Override
	public void delete(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " DELETE FROM history WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate();
			if (result != 1) {
				ps.setInt(1, history.getReservation_id());
				ps.setInt(2, history.getPatient_id());
				ps.setInt(3, history.getDoctor_id());
				ps.setString(4, history.getComment());
				ps.setInt(5, history.getBill_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();

		}
	}

	@Override
	public void update(History o) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "UPDATE history SET reservation_id = '"
					+ o.getReservation_id() + " ', patient_id = '"
					+ o.getPatient_id() + " ', doctor_id = '"
					+ o.getDoctor_id() + " ', comment = '" + o.getComment()
					+ " ', bill_id = '" + o.getBill_id() + "' WHERE id = "
					+ o.getId();
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate(sql);

			if (result != 1) {
				ps.setInt(1, o.getReservation_id());
				ps.setInt(2, o.getPatient_id());
				ps.setInt(3, o.getDoctor_id());
				ps.setString(4, o.getComment());
				ps.setInt(5, o.getBill_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
	}
}
